﻿using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;
using System.Data.OleDb;
using System.IO.Ports;
using System.Text.RegularExpressions;
using System.Media;
using System.Speech.Synthesis;

namespace NewLandOY20
{
    public partial class InBoxForm : Form
    {
        public InBoxForm()
        {
            InitializeComponent();
            today = DateTime.Now.ToString("yyyy-MM-dd");
        }
        
        // 变量定义
        int maxCount = 0;
        int Count = 0;
        string filePath = "";                                   // 数据文件路径
        string soundCompletePath = System.Windows.Forms.Application.StartupPath + "\\sounds\\complete.wav";
        string today = "";
        AccessHelper accessHelper = null;                 // 数据库连接对象
        MySerialConnect serialConnect = null;                   // 串口连接对象
        DataTable tableUserInfo = null;
        SpeechSynthesizer speech = null;


        /// <summary>
        /// 串口刷新信息的方法
        /// MySerialConnect对象委托调用该方法
        /// 在窗口显示串口读取到的信息
        /// </summary>
        /// <param name="info"></param>
        private void showSerialInfos(string info)
        {
            Invoke( new Action(()=>{
                textBox_SerialInfoShow.Text = info;
            }));
        }

        /// <summary>
        /// 连接到数据库，并设置数据库的连接状态图标
        /// </summary>
        private void openDatabase()
        {
            if(accessHelper != null)
            {
                accessHelper.DisConnect();
                accessHelper = null;
            }
            accessHelper = new AccessHelper(filePath, "");
            if (accessHelper.Connect())
            {
                // 数据库查询测试，将Box表显示到DataGridView
                //selectAndRefershData(MyAccessSQLStatement.SelectBoxWithOutInvoice);
                // 设置数据库连接状态
                string iconPath = System.Windows.Forms.Application.StartupPath + "\\icons\\connect.png";
                pictureBox_databaseStatus.Image = Image.FromFile(iconPath);
                label_filePath.Text = filePath;
                label_databaseStatus.Text = "已连接";
            }
            else
            {
                // 设置数据库连接状态
                string iconPath = System.Windows.Forms.Application.StartupPath + "\\icons\\disConnect.png";
                pictureBox_databaseStatus.Image = Image.FromFile(iconPath);
                label_filePath.Text = filePath;
                label_databaseStatus.Text = "未连接";
            }
            getUserInfoToComboBox();
        }

        /// <summary>
        /// 获取所有的用户信息并保存在tableUserInfo
        /// </summary>
        private void getUserInfoToComboBox()
        {
            tableUserInfo = accessHelper.ExecuteDataTable(MyAccessSQLStatement.SelectAllUser, null);
            if (tableUserInfo != null)
            {
                comboBox_user.Items.Clear();
                foreach (DataRow item in tableUserInfo.Rows)
                {
                    StringBuilder addStr = new StringBuilder(item["收货人姓名"].ToString());
                    addStr.Append(",");
                    addStr.Append(item["联系电话"]);
                    addStr.Append(",");
                    addStr.Append(item["公司"]);
                    addStr.Append(",");
                    addStr.Append(item["收货地址"]);
                    comboBox_user.Items.Add(addStr.ToString());
                }
            }
        }

        /// <summary>
        /// 查询数据库，并将数据显示到UI
        /// </summary>
        /// <param name="sqlStr">数据库查询语句</param>
        private int selectAndRefershData(string sqlStr)
        {
            DataTable table = null;
            if(accessHelper != null)
                table = accessHelper.ExecuteDataTable(sqlStr, null);
            if (table != null)
            {
                dataGridView.DataSource = table;
                return table.Rows.Count;
            }
            return -1;
        }
        
        /// <summary>
        /// 将表号插入数据库
        /// </summary>
        /// <param name="meterNum">要插入的表号</param>
        private void writeMeterNumToDatabase(string meterNum)
        {
            // 将表号写入数据库
            if(accessHelper != null && meterNum != "" && textBox_boxNum.Text != "")
            {
                StringBuilder sqlStr = new StringBuilder("INSERT INTO METER VALUES('");
                // 表号
                sqlStr.Append(meterNum);
                sqlStr.Append("','");
                // 入库时间
                sqlStr.Append(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                sqlStr.Append("','");
                // 仪表放入的箱号
                sqlStr.Append(textBox_boxNum.Text);
                sqlStr.Append("');");

                int changeCount = accessHelper.ExecuteNonQuery(sqlStr.ToString(), null);

                // 刷新dataGridView的显示
                selectAndRefershData(MyAccessSQLStatement.SelectBoxAndMeter + "WHERE Meter.表扫描时间>#" + today + "# " + MyAccessSQLStatement.OrderByMeterDesc);
                
                if(changeCount > 0)
                {
                    // 将入库完成的表号添加到ListView
                    listBox_meterNums.Items.Add(meterNum);
                    label_count.Text = listBox_meterNums.Items.Count.ToString();
                }
            }
        }

        /// <summary>
        /// 将箱号插入数据库
        /// </summary>
        /// <param name="boxNum">要入库的箱号</param>
        private void writeBoxNumToDatabase(string boxNum)
        {
            // 将箱号写入数据库
            if (accessHelper != null && boxNum != "")
            {
                StringBuilder sqlStr = new StringBuilder("INSERT INTO BOX(箱号, 箱扫描时间) VALUES ('");
                sqlStr.Append(boxNum);
                sqlStr.Append("','");
                sqlStr.Append(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                sqlStr.Append("');");

                accessHelper.ExecuteNonQuery(sqlStr.ToString(), null);

                // 刷新dataGridView的显示
                selectAndRefershData(MyAccessSQLStatement.SelectBoxWithOutInvoice + " AND Box.箱扫描时间>#" + today + "# " + MyAccessSQLStatement.OrderByBoxScanTimeDesc);
                if (textBox_invoice.Text == "")
                {
                    // 显示新扫描到的箱号，并清除表号列表
                    textBox_boxNum.Text = boxNum;
                    listBox_meterNums.Items.Clear();
                    label_count.Text = "0";
                    Count = 0;
                }
            }
        }

        /// <summary>
        /// 更新数据库中箱号和订单号的联系
        /// </summary>
        /// <param name="boxNum">要修改的箱号</param>
        /// <param name="invoiceNum">该箱号对应的订单号</param>
        private void updateBoxInvoiceToDatabase(string boxNum, string invoiceNum)
        {
            StringBuilder sqlStr = new StringBuilder("UPDATE Box SET 订单号='");
            sqlStr.Append(invoiceNum);
            sqlStr.Append("' WHERE 箱号='");
            sqlStr.Append(boxNum);
            sqlStr.Append("';");

            accessHelper.ExecuteNonQuery(sqlStr.ToString(), null);

            // 刷新dataGridView的显示
            selectAndRefershData(MyAccessSQLStatement.SelectInvoiceAndBox + MyAccessSQLStatement.OrderByBoxScanTimeDesc);

            listBox_meterNums.Items.Add(boxNum);
            label_count.Text = listBox_meterNums.Items.Count.ToString();
        }

        
        /// <summary>
        /// 打开串口按钮的点击事件
        /// 打开串口设置窗体
        /// 读取配置文件中的串口配置信息，并连接到串口
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_openSerial_Click(object sender, EventArgs e)
        {
            SetSerialForm setForm = new SetSerialForm();
            if(setForm.ShowDialog() == DialogResult.OK)
            {
                serialConnect = new MySerialConnect();
                //读取配置文件，连接串口
                string iPort = MyConfigHelper.getConfigValue(MyConstant.iConfigPort);
                int iBaud = Convert.ToInt32(MyConfigHelper.getConfigValue(MyConstant.iConfigBaud));
                int iDataBits = Convert.ToInt16(MyConfigHelper.getConfigValue(MyConstant.iConfigData));
                Parity iParity = Parity.None;
                // 设置校验位
                switch (MyConfigHelper.getConfigValue(MyConstant.iConfigCheck))
                {
                    case "0": iParity = Parity.Even; break;
                    case "1": iParity = Parity.Mark; break;
                    case "2": iParity = Parity.None; break;
                    case "3": iParity = Parity.Odd; break;
                    case "4": iParity = Parity.Space; break;
                }
                // 设置停止位
                StopBits iStopBits = StopBits.One;
                switch (MyConfigHelper.getConfigValue(MyConstant.iConfigStop))
                {
                    case "1": iStopBits = StopBits.One; break;
                    case "1.5": iStopBits = StopBits.OnePointFive; break;
                    case "2": iStopBits = StopBits.Two; break;
                }
                // 设置委托方法，将数据显示在TextView中
                serialConnect.myShowMsgDelegate += showSerialInfos;
                //开启串口
                serialConnect.OpenPort(iPort, iBaud, iDataBits, iParity, iStopBits);


                // 串口打开成功后将打开串口按钮设置为不可见 关闭串口设置为可见
                button_closeSerial.Enabled = true;
                button_openSerial.Enabled = false;
            }
           
        }

        /// <summary>
        /// 关闭串口按钮的点击事件
        /// 关闭串口
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_closeSerial_Click(object sender, EventArgs e)
        {
            if(serialConnect != null)
            {
                serialConnect.ClosePort();
            }


            // 串口打开成功后将打开串口按钮设置为可见 关闭串口设置为不可见
            button_closeSerial.Enabled = false;
            button_openSerial.Enabled = true;
        }

        /// <summary>
        /// 获取数据库所在路径，并连接数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MainForm_Load(object sender, EventArgs e)
        {
            //读取上次数据库配置文件所在路径，尝试显示数据
            filePath = MyConfigHelper.getConfigValue(MyConstant.iConfigFilePath);
            if(filePath != null && filePath != "")
            {
                // 连接到数据库
                openDatabase();
                // 设置程序初始化后要显示的数据
                selectAndRefershData(MyAccessSQLStatement.SelectBoxWithOutInvoice);
                comboBox_data.SelectedIndex = 3;
            }
            label_filePath.Text = filePath;
            speech = new SpeechSynthesizer();
            speech.Rate = 3;
            speech.Volume = 100;
            button_closeSerial.Enabled = false;
            maxCount = Convert.ToInt32(MyConfigHelper.getConfigValue(MyConstant.iConfigMaxCount));
            textBox_maxCount.Text = maxCount.ToString();
        }

        /// <summary>
        /// 通过openfileDialog选择程序使用的数据库文件
        /// 并将选择的文件路径保存在配置文件，使程序下次启动时直接连接到数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_openFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "Access数据库文件|*.mdb";

            if(dialog.ShowDialog() == DialogResult.OK)
            {
                filePath = dialog.FileName;
                MyConfigHelper.setConfigValue(MyConstant.iConfigFilePath, filePath);
                openDatabase();
            }
        }

        /// <summary>
        /// 在程序关闭时，关闭数据库连接
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void MainForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            if(accessHelper != null)
            {
                accessHelper.DisConnect();
            }
            maxCount = Convert.ToInt32(textBox_maxCount.Text);
            MyConfigHelper.setConfigValue(MyConstant.iConfigMaxCount, maxCount.ToString());
        }

        /// <summary>
        /// 通过更改不同的选择项 选择显示不同的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //today = DateTime.Now.ToString("yyyy-MM-dd");
            switch(comboBox_data.SelectedIndex)
            {
                // 箱号与订单号
                case 0: selectAndRefershData(MyAccessSQLStatement.SelectInvoiceAndBox + MyAccessSQLStatement.OrderByInvoiceDesc); break;
                // 箱号与表号
                case 1: selectAndRefershData(MyAccessSQLStatement.SelectBoxAndMeter + MyAccessSQLStatement.OrderByMeterDesc); break;
                // 订单号与客户信息
                case 2: selectAndRefershData(MyAccessSQLStatement.SelectInvoiceAndUser + MyAccessSQLStatement.OrderByInvoiceDesc); break;
                // 无订单箱号
                case 3: selectAndRefershData(MyAccessSQLStatement.SelectBoxWithOutInvoice + MyAccessSQLStatement.OrderByBoxScanTimeDesc); break;
                // 所有箱号
                case 4: selectAndRefershData(MyAccessSQLStatement.SelectBox + MyAccessSQLStatement.OrderByBoxScanTimeDesc); break;
                // 所有用户
                case 5: selectAndRefershData(MyAccessSQLStatement.SelectAllUser); break;
            }
        }
        /// <summary>
        /// 开启添加用户窗体
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_createUser_Click(object sender, EventArgs e)
        {
            AddUserForm addUserForm = new AddUserForm();
            addUserForm.Helper = accessHelper;
            addUserForm.ShowDialog();
            getUserInfoToComboBox();
        }

        /// <summary>
        /// 验证输入的单号是否合法
        /// 获取选择的收货人信息， 
        /// 并写入数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_createInvoice_Click(object sender, EventArgs e)
        {
            string invoiceStr = textBox_invoice.Text;
            int selectIndex = comboBox_user.SelectedIndex;
            if(invoiceStr != "" && comboBox_user.SelectedIndex != -1
                && Regex.IsMatch(invoiceStr, MyPatternStr.RegexInvoice))
            {
                StringBuilder sqlStr = new StringBuilder("INSERT INTO Invoice VALUES('");
                sqlStr.Append(invoiceStr);
                sqlStr.Append("','");
                // 获取并添加用户号
                sqlStr.Append(tableUserInfo.Rows[selectIndex]["用户号"]);
                sqlStr.Append("','");
                sqlStr.Append(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
                sqlStr.Append("');");
                //MessageBox.Show(sqlStr.ToString());

                int changeCount = accessHelper.ExecuteNonQuery(sqlStr.ToString(), null);
                if(changeCount > 0)
                {
                    //MessageBox.Show("添加成功！", "提示");
                    selectAndRefershData(MyAccessSQLStatement.SelectInvoiceAndUser + MyAccessSQLStatement.OrderByInvoiceDesc);
                }
            }
        }

        /// <summary>
        /// 清除箱号
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void textBox_invoice_TextChanged(object sender, EventArgs e)
        {
            listBox_meterNums.Items.Clear();
            label_count.Text = "0";
        }
        /// <summary>
        /// 搜索箱号按钮的点击事件
        /// 验证输入的箱号是否符合规则
        /// 根据输入框中的箱号搜索订单信息
        /// 分为两种情况，
        /// 1.箱号与订单号有联系
        /// 2.箱号还未与订单号联系
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_searchBox_Click(object sender, EventArgs e)
        {
            string serachStr = textBox_search.Text;
            if(serachStr != "" && Regex.IsMatch(serachStr, MyPatternStr.RegexBox))
            {
                StringBuilder sqlStr = new StringBuilder(MyAccessSQLStatement.SelectBoxAndMeter);
                sqlStr.Append("WHERE BOX.[箱号]='");
                sqlStr.Append(serachStr);
                sqlStr.Append("';");
                //MessageBox.Show(sqlStr.ToString());
                selectAndRefershData(sqlStr.ToString());
            }
        }

        /// <summary>
        /// 按照表号搜索数据库
        /// 分为两种情况，
        /// 1.表号所在的箱号已和订单号关联
        /// 2.表号所在的箱号未和订单号关联
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_searchMeter_Click(object sender, EventArgs e)
        {
            string searchMeter = textBox_search.Text;
            if (searchMeter != "" && Regex.IsMatch(searchMeter, MyPatternStr.RegexMeter))
            {
                // 查询箱号是否与一个订单有关系
                StringBuilder sqlStr = new StringBuilder(MyAccessSQLStatement.SelectMeterWithInvoice);
                sqlStr.Append(searchMeter);
                sqlStr.Append("' ORDER BY Invoice.订单号 DESC;");

                int count = selectAndRefershData(sqlStr.ToString());

                // 未查询到数据，使用其他语句进行查询
                if (count <= 0)
                {
                    sqlStr = new StringBuilder(MyAccessSQLStatement.SelectBoxAndMeter);
                    sqlStr.Append(" WHERE (Meter.表号)='");
                    sqlStr.Append(searchMeter);
                    sqlStr.Append("' ");
                    sqlStr.Append(MyAccessSQLStatement.OrderByInvoiceInBoxDes);

                    selectAndRefershData(sqlStr.ToString());
                }

            }
        }

        /// <summary>
        /// 可以按照用户号和收货人姓名进行搜索信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_searchUser_Click(object sender, EventArgs e)
        {
            string serachStr = textBox_search.Text;
            if (serachStr != "")
            {
                StringBuilder sqlStr = new StringBuilder(MyAccessSQLStatement.SelectUserAndInvoice);
                
                if(Regex.IsMatch(serachStr,MyPatternStr.RegexUser))     // 如果输入的是用户编号
                {
                    sqlStr.Append("WHERE [User].用户号='");
                    sqlStr.Append(serachStr);
                    sqlStr.Append("';");
                }
                else                // 如果输入的是其他内容，按照输入的是收货人姓名处理
                {
                    sqlStr.Append("WHERE [User].收货人姓名='");
                    sqlStr.Append(serachStr);
                    sqlStr.Append("';");
                }
                int count = selectAndRefershData(sqlStr.ToString());
                if(count <= 0)
                {
                    sqlStr = new StringBuilder("SELECT * FROM [User] ");
                    if (Regex.IsMatch(serachStr, MyPatternStr.RegexUser))     // 如果输入的是用户编号
                    {
                        sqlStr.Append("WHERE [User].用户号='");
                        sqlStr.Append(serachStr);
                        sqlStr.Append("';");
                    }
                    else                // 如果输入的是其他内容，按照输入的是收货人姓名处理
                    {
                        sqlStr.Append("WHERE [User].收货人姓名='");
                        sqlStr.Append(serachStr);
                        sqlStr.Append("';");
                    }
                    selectAndRefershData(sqlStr.ToString());
                }
            }
        }

        /// <summary>
        /// 搜索单号，按照输入的单号搜索信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button_searchInvoice_Click(object sender, EventArgs e)
        {
            string serachStr = textBox_search.Text;
            if(serachStr != "" && Regex.IsMatch(serachStr, MyPatternStr.RegexInvoice))
            {
                StringBuilder sqlStr = new StringBuilder(MyAccessSQLStatement.SelectInvoiceBoxUser);
                sqlStr.Append("WHERE Invoice.订单号='");
                sqlStr.Append(serachStr);
                sqlStr.Append("';");

                selectAndRefershData(sqlStr.ToString());
            }
        }

        /// <summary>
        /// 单击扫描结果输入框 全选其中内容
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void textBox_SerialInfoShow_MouseClick(object sender, MouseEventArgs e)
        {
            textBox_SerialInfoShow.SelectAll();
        }

        /// <summary>
        /// 达到最大计数值时发出声音
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void label_count_TextChanged(object sender, EventArgs e)
        {
            Count = Convert.ToInt32(label_count.Text);
            maxCount = Convert.ToInt32(textBox_maxCount.Text);
            if ((Count == maxCount || Count == maxCount-4) && maxCount != 0)
            {
                //System.Media.SystemSounds.Beep.Play();
                speech.SpeakAsync("已扫描" + label_count.Text + "个");
            }

        }
        /// <summary>
        /// 最大计数值只允许输入数字
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void textBox_maxCount_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar != '\b')//这是允许输入退格键
            {
                if ((e.KeyChar < '0') || (e.KeyChar > '9'))//这是允许输入0-9数字
                {
                    e.Handled = true;
                }
            }
        }

        /// <summary>
        /// 检测输入的按键，输入为回车时 进行正则字符串匹配
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void textBox_SerialInfoShow_KeyPress(object sender, KeyPressEventArgs e)
        {
            if(e.KeyChar == 13)
            {
                // 扫描到的字符串为箱号时
                string scanStr = textBox_SerialInfoShow.Text;
                
                // 订单号不为空，将箱号和订单号连接起来 数据库改操作
                if (Regex.IsMatch(textBox_invoice.Text, MyPatternStr.RegexInvoice) &&
                    listBox_meterNums.FindString(scanStr) == -1 &&
                    Regex.IsMatch(scanStr, MyPatternStr.RegexBox))
                {
                    DataTable table = null;
                    table = accessHelper.ExecuteDataTable("SELECT BOX.* FROM BOX WHERE BOX.箱号='" + scanStr + "';", null);
                    if(table.Rows.Count > 0)
                    {
                        updateBoxInvoiceToDatabase(textBox_SerialInfoShow.Text, textBox_invoice.Text);
                    }
                    else        // 如果箱号不存在 新建一个箱号 并关联
                    {
                        writeBoxNumToDatabase(scanStr);
                        updateBoxInvoiceToDatabase(textBox_SerialInfoShow.Text, textBox_invoice.Text);
                    }
                }
                else if (Regex.IsMatch(scanStr, MyPatternStr.RegexBox) && textBox_invoice.Text == "")     // 正则表达式解析获取的字符串是否是箱号
                {
                    //MessageBox.Show("获取到箱号");
                    writeBoxNumToDatabase(scanStr);
                } 
                else if (Regex.IsMatch(scanStr, MyPatternStr.RegexMeter))      //箱号正则表达式
                {
                    if (Count < maxCount)   // 计数值小于最大计数值 允许录入
                    {
                        writeMeterNumToDatabase(scanStr);
                    }
                    else
                    {
                        // 
                        //System.Media.SystemSounds.Beep.Play();
                        speech.SpeakAsync("已扫描" + label_count.Text + "个");
                    }
                }
                // 取消输入回车后的咚声
                e.Handled = true;
                // 输入回车后选择已输入的文本
                textBox_SerialInfoShow.SelectAll();
            }
        }
    }
}
